from flask import jsonify, request

# 导入连接数据库的组件
from public.sql_connect import get_connent

# 公共模块--商品详情
def good_get_public(good_id):
    conn = get_connent()
    cursor = conn.cursor(dictionary=True)
    if good_id is None:
        return jsonify({
            "code": -1,
            "msg": "请提供目标商品的id",
            "data": None
        })
    try:
        cursor.execute(
        '''
            SELECT 
            goods.id, goods.name, goods.detail, goods.picture, goods.price, goods.discount, goods.oldPrice, goods.inventory, goods.user_id, goods.created_at, goods.updated_at, goods.brandId, goods.salesCount, goods.commentCount, goods.collectCount, goods.available, goods.sort_id, users.account as user_account, users.mobile as user_mobile, users.nickname as user_nickname, brands.name as brands_name, brands.nameEn as brands_name_en, sorts.name as sorts_name, sorts.nameEn as sorts_name_en
            FROM goods
            LEFT JOIN users ON goods.user_id = users.id
            LEFT JOIN brands ON goods.brandId = brands.id
            LEFT JOIN sorts ON goods.sort_id = sorts.id
            WHERE goods.id = %s
        ''',(good_id,))
        data = cursor.fetchone()
        if data is None:
            return jsonify({
            "code": -1,
            "msg": "获取数据为空",
            "data": None
        })
        return jsonify({
            "code": 0,
            "msg": "商品详情获取成功",
            "data": {
                "data": data,
            }
        })
    except Exception as e:
        return jsonify({"code": -1, "msg": f"数据库错误: {str(e)}", "data": None})
    finally:
        cursor.close()
        conn.close()


def good_get_public_login(current_user_id, good_id):
    conn = get_connent()
    cursor = conn.cursor(dictionary=True)
    if good_id is None:
        return jsonify({
            "code": -1,
            "msg": "请提供目标商品的id",
            "data": None
        })
    try:
        # 在浏览记录中+1
        # 先查看用户是否已经浏览过
        cursor.execute(
            "SELECT id FROM views WHERE user_id = %s AND good_id = %s",
            (current_user_id, good_id)
        )
        check_res = cursor.fetchone()
        if check_res is None:
            cursor.execute(
                "INSERT INTO views (user_id, good_id, view_num, created_at, updated_at) VALUES (%s, %s, 1, NOW(), NOW())",
                (current_user_id, good_id)
            )
            conn.commit()
        else:
            cursor.execute(
                "UPDATE views SET view_num = view_num + 1 WHERE user_id = %s AND good_id = %s",
                (current_user_id, good_id)
            )
            conn.commit()

        cursor.execute(
            '''
            SELECT goods.id,
                   goods.name,
                   goods.detail,
                   goods.picture,
                   goods.price,
                   goods.discount,
                   goods.oldPrice,
                   goods.inventory,
                   goods.user_id,
                   goods.created_at,
                   goods.updated_at,
                   goods.brandId,
                   goods.salesCount,
                   goods.commentCount,
                   goods.collectCount,
                   goods.available,
                   goods.sort_id,
                   goods.size,
                   goods.color,
                   users.account  as user_account,
                   users.mobile   as user_mobile,
                   users.nickname as user_nickname,
                   brands.name    as brands_name,
                   brands.nameEn  as brands_name_en,
                   sorts.name     as sorts_name,
                   sorts.nameEn   as sorts_name_en
            FROM goods
                     LEFT JOIN users ON goods.user_id = users.id
                     LEFT JOIN brands ON goods.brandId = brands.id
                     LEFT JOIN sorts ON goods.sort_id = sorts.id
            WHERE goods.id = %s
            ''', (good_id,))
        data = cursor.fetchone()
        if data is None:
            return jsonify({
                "code": -1,
                "msg": "获取数据为空",
                "data": None
            })
        return jsonify({
            "code": 0,
            "msg": "商品详情获取成功",
            "data": {
                "data": data,
            }
        })
    except Exception as e:
        return jsonify({"code": -1, "msg": f"数据库错误: {str(e)}", "data": None})
    finally:
        cursor.close()
        conn.close()


# 公共模块--商品列表
def good_list_public():
    data = request.get_json()
    if not data:
        return jsonify({"code": -1, "msg": "请求数据不能为空", "data": None}), 400

    # 获取分页参数
    page = int(data.get('page', 1))
    page_size = int(data.get('page_size', 10))
    offset = (page - 1) * page_size

    # 获取筛选参数
    keywords = data.get('keywords', '')
    price_start = float(data.get('price_start', 0))
    price_end = float(data.get('price_end', 0))
    brandId = data.get('brandId', '')
    sort_id = data.get('sort_id', '')

    # 获取排序参数
    is_sales_priority = data.get('is_sales_priority', False)
    is_price_priority = data.get('is_price_priority', False)
    is_comment_priority = data.get('is_comment_priority', False)

    conn = get_connent()
    cursor = conn.cursor(dictionary=True)

    try:
        params = []
        count_params = []

        # 基础查询SQL
        sql = '''
              SELECT goods.id, 
                     goods.name, 
                     goods.detail, 
                     goods.picture, 
                     goods.price, 
                     goods.discount, 
                     goods.oldPrice, 
                     goods.inventory, 
                     goods.user_id, 
                     goods.created_at, 
                     goods.updated_at, 
                     goods.brandId, 
                     goods.salesCount, 
                     goods.commentCount, 
                     goods.collectCount, 
                     goods.available, 
                     goods.sort_id, 
                     users.account     as user_account, 
                     users.mobile      as user_mobile, 
                     users.nickname    as user_nickname, 
                     brands.name       as brands_name, 
                     brands.nameEn     as brands_name_en, 
                     sorts.name        as sorts_name, 
                     sorts.nameEn      as sorts_name_en, 
                     CASE WHEN goods.price < goods.oldPrice THEN 1 ELSE 0 END as is_discount
              FROM goods
                       LEFT JOIN users ON goods.user_id = users.id
                       LEFT JOIN brands ON goods.brandId = brands.id
                       LEFT JOIN sorts ON goods.sort_id = sorts.id
              WHERE goods.available = 'true' 
              '''

        count_sql = '''
                    SELECT COUNT(*) as total
                    FROM goods
                             LEFT JOIN users ON goods.user_id = users.id
                             LEFT JOIN brands ON goods.brandId = brands.id
                             LEFT JOIN sorts ON goods.sort_id = sorts.id
                    WHERE goods.available = 'true' 
                    '''

        # 添加筛选条件
        if keywords:
            sql += " AND (goods.name LIKE %s OR goods.detail LIKE %s)"
            count_sql += " AND (goods.name LIKE %s OR goods.detail LIKE %s)"
            params.extend([f'%{keywords}%', f'%{keywords}%'])
            count_params.extend([f'%{keywords}%', f'%{keywords}%'])

        if price_start > 0:
            sql += " AND goods.price >= %s"
            count_sql += " AND goods.price >= %s"
            params.append(price_start)
            count_params.append(price_start)

        if price_end > 0:
            sql += " AND goods.price <= %s"
            count_sql += " AND goods.price <= %s"
            params.append(price_end)
            count_params.append(price_end)

        if brandId:
            sql += " AND goods.brandId = %s"
            count_sql += " AND goods.brandId = %s"
            params.append(brandId)
            count_params.append(brandId)

        if sort_id:
            sql += " AND goods.sort_id = %s"
            count_sql += " AND goods.sort_id = %s"
            params.append(sort_id)
            count_params.append(sort_id)

        # 添加排序条件
        order_clauses = []
        if is_sales_priority:
            order_clauses.append("goods.salesCount DESC")
        if is_price_priority:
            order_clauses.append("goods.price ASC")  # 价格从低到高
        if is_comment_priority:
            order_clauses.append("goods.commentCount DESC")

        # 默认排序（当没有指定优先级时）
        if not order_clauses:
            order_clauses.append("goods.created_at DESC")

        sql += " ORDER BY " + ", ".join(order_clauses)
        sql += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])

        # 执行查询
        cursor.execute(sql, params)
        list_data = cursor.fetchall()

        cursor.execute(count_sql, count_params)
        total = cursor.fetchone().get('total')

        return jsonify({
            "code": 0,
            "msg": "商品列表获取成功",
            "data": {
                "list": list_data,
                "total": total,
                "page": page,
                "page_size": page_size,
                "filters": {
                    "keywords": keywords,
                    "price_range": [price_start, price_end],
                    "brandId": brandId,
                    "sort_id": sort_id,
                    "sorting": {
                        "by_sales": is_sales_priority,
                        "by_price": is_price_priority,
                        "by_comments": is_comment_priority
                    }
                }
            }
        })

    except Exception as e:
        return jsonify({
            "code": -1,
            "msg": f"数据库错误: {str(e)}",
            "data": None
        }), 500

    finally:
        cursor.close()
        conn.close()



# 公共模块--商品评论列表
def comment_list_public(good_id):
    data = request.get_json()
    page = int(data.get('page', '1'))
    page_size = int(data.get('page_size', '10'))
    offset = (page - 1) * page_size
    if not good_id:
        return jsonify({
            "code": -1,
            "msg": "请提供商品ID",
            "data": None
        })
    conn = get_connent()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
                '''
                    SELECT 
                       c.content, c.id, c.picture , u.account, u.picture, c.created_at
                    FROM comments c
                    LEFT JOIN users u ON c.user_id = u.id
                    LEFT JOIN user_goods ug ON u.id = ug.user_id AND c.buy_id = ug.id
                    WHERE ug.good_id = %s 
                    ORDER BY c.id DESC
                    LIMIT %s OFFSET %s
                ''',
            (good_id, page_size, offset)
        )
        comment_list_all = cursor.fetchall()
        return jsonify({
            "code": 0,
            "msg": "商品评论获取成功",
            "data": {
                "comment_list": comment_list_all,
            }
        })
    except Exception as e:
        conn.rollback()
        return jsonify({
            "code": -1,
            "msg": f"数据库错误: {str(e)}",
            "data": None
        }), 500
    finally:
        cursor.close()
        conn.close()


# 品牌的列表
def brand_list_public():
    conn = get_connent()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            "SELECT id, name, nameEn FROM brands ORDER BY id DESC"
        )
        brand_list = cursor.fetchall()
        cursor.execute(
            "SELECT COUNT(id) as brand_total FROM brands"
        )
        brand_total = cursor.fetchone()['brand_total']
        return jsonify({
            "code": 0,
            "msg": "品牌列表请求成功",
            "data": {
                "brand_list": brand_list,
                "brand_total": brand_total
            }
        })
    except Exception as e:
        conn.rollback()
        return jsonify({
            "code": -1,
            "msg": f"数据库错误: {str(e)}",
            "data": None
        }), 500
    finally:
        cursor.close()
        conn.close()


# 品牌的列表
def brand_list_public_page():
    date = request.get_json()
    page = int(date.get('page', '1'))
    page_size = int(date.get('page_size', '10'))
    offset = (page - 1) * page_size
    conn = get_connent()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            "SELECT id, name, nameEn FROM brands ORDER BY id DESC LIMIT %s OFFSET %s",
            (page_size, offset)
        )
        brand_list = cursor.fetchall()
        cursor.execute(
            "SELECT COUNT(id) as brand_total FROM brands"
        )
        brand_total = cursor.fetchone()['brand_total']
        return jsonify({
            "code": 0,
            "msg": "品牌列表请求成功",
            "data": {
                "brand_list": brand_list,
                "brand_total": brand_total
            }
        })
    except Exception as e:
        conn.rollback()
        return jsonify({
            "code": -1,
            "msg": f"数据库错误: {str(e)}",
            "data": None
        }), 500
    finally:
        cursor.close()
        conn.close()



# 分类的列表
# 获取公开的商品分类列表（树形结构）
def sort_list_public():
    """
    功能说明：
    从数据库中查询所有商品分类，并将其整理成层级树形结构返回。
    返回格式为：
    {
        "code": 0,
        "msg": "商品分类列表请求成功",
        "data": {
            "sort_list": [...],  # 树形结构的分类列表
            "sort_total": 总分类数
        }
    }
    """
    # 获取数据库连接
    conn = get_connent()
    # 创建游标，返回结果为字典格式（字段名作为键）
    cursor = conn.cursor(dictionary=True)

    try:
        # 第一步：查询所有分类，按父级ID升序、自身ID降序排序
        # 这样能保证父级分类出现在子级分类之前，便于后续构建树
        cursor.execute(
            "SELECT id, name, nameEn, father_id FROM sorts ORDER BY father_id ASC, id DESC"
        )
        all_sorts = cursor.fetchall()

        # 第二步：查询总分类数，用于前端展示
        cursor.execute("SELECT COUNT(id) as sort_total FROM sorts")
        sort_total = cursor.fetchone()['sort_total']

        # 第三步：初始化两个辅助数据结构
        # sort_dict: 以分类ID为键，存储所有分类的详细信息
        sort_dict = {}
        # children_map: 以分类ID为键，存储该分类下的所有子分类
        children_map = {}

        # 初始化阶段：填充 sort_dict 和 children_map
        for sort in all_sorts:
            sort_id = sort['id']
            sort_dict[sort_id] = sort  # 存储分类信息
            children_map[sort_id] = []  # 初始化子分类列表为空

        # 第四步：建立父子关系映射
        # 遍历所有分类，将子分类添加到父分类的 children_map 中
        for sort in all_sorts:
            father_id = sort['father_id']
            # 父分类存在且不是顶级分类（father_id != 0）
            if father_id != 0 and father_id in sort_dict:
                children_map[father_id].append(sort)

        # 第五步：找出所有顶级分类（father_id == 0）
        top_level_sorts = []
        for sort in all_sorts:
            if sort['father_id'] == 0:
                # 初始化子分类列表
                sort['children'] = []
                # 这里已经添加了一层了，也就是以及的菜单
                top_level_sorts.append(sort)

        # 第六步：递归构建树形结构
        def build_tree(parent_id):
            """
            递归函数：根据父分类ID，构建其所有子分类的树形结构
            参数：
                parent_id: 父分类的ID
            返回：
                子分类列表（每个子分类包含其自身的子分类）
            """
            # 这里就是对父级的子集内容进行检查，
            children = []
            # 获取当前父分类的所有子分类
            for child in children_map.get(parent_id, []):
                # 创建子分类的副本，避免修改原始数据
                child_copy = child.copy()
                # 递归构建子分类的子分类
                child_copy['children'] = build_tree(child['id'])
                children.append(child_copy)
            return children


        # 第七步：为每个顶级分类构建完整的子树
        for top_sort in top_level_sorts:
            # 为每个一级菜单发配子集菜单
            top_sort['children'] = build_tree(top_sort['id'])

        # 最终返回结构化的JSON响应
        return jsonify({
            "code": 0,
            "msg": "商品分类列表请求成功",
            "data": {
                "sort_list": top_level_sorts,
                "sort_total": sort_total
            }
        })

    except Exception as e:
        # 发生异常时回滚事务，并返回错误信息
        conn.rollback()
        return jsonify({
            "code": -1,
            "msg": f"数据库错误: {str(e)}",
            "data": None
        }), 500
    finally:
        # 无论是否发生异常，最后都要关闭游标和连接
        cursor.close()
        conn.close()




# 分类的列表
def sort_list_public_page():
    date = request.get_json()
    page = int(date.get('page', '1'))
    page_size = int(date.get('page_size', '10'))
    offset = (page - 1) * page_size

    conn = get_connent()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            "SELECT id, name, nameEn FROM sorts ORDER BY id DESC LIMIT %s OFFSET %s",
            (page_size, offset)
        )
        sort_list_data = cursor.fetchall()
        cursor.execute(
            "SELECT COUNT(id) as sort_total FROM sorts"
        )
        sort_total = cursor.fetchone()['sort_total']
        return jsonify({
            "code": 0,
            "msg": "商品分类列表请求成功",
            "data": {
                "sort_list": sort_list_data,
                "sort_total": sort_total
            }
        })
    except Exception as e:
        conn.rollback()
        return jsonify({
            "code": -1,
            "msg": f"数据库错误: {str(e)}",
            "data": None
        }), 500
    finally:
        cursor.close()
        conn.close()


